package com.etc.emsys.dao.emp;

import com.etc.emsys.dao.JDBCutil;
import com.etc.emsys.domain.EmpPojo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmpDao {

    public List<EmpPojo> searchByCondition(String ename, int deptno) {
        List<EmpPojo> elist = new ArrayList<>();

        try (Connection conn = JDBCutil.getConnection();) {
            StringBuilder sb = new StringBuilder();
            sb.append(" select t1.empno, t1.ename, t2.deptno, t2.dname\n" +
                    "from emp t1\n" +
                    "inner join dept t2 on t1.deptno = t2.deptno\n" +
                    "where 1 = 1\n");

            if (ename != null && !"".equals(ename)) {
                sb.append("and  t1.ename like concat(concat('%',?), '%')\n");
            }
            if (deptno != 0) {
                sb.append("and  t1.deptno = ? ");
            }
            PreparedStatement preparedStatement = conn.prepareStatement(sb.toString());

            int index = 0;
            if (ename != null && !"".equals(ename)) {
                preparedStatement.setString(++index, ename);
            }
            if (deptno != 0) {
                preparedStatement.setInt(++index, deptno);
            }

            //执行sql语句
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                EmpPojo pojo = new EmpPojo();
                pojo.setEname(rs.getString("ename"));
                pojo.setEmpno(rs.getInt("empno"));
                pojo.setDname(rs.getString("dname"));
                elist.add(pojo);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return elist;
    }

    /**
     * 删除 员工
     *
     * @param empno 员工号
     */
    public void deleteByEmpno(int empno) {
        try (Connection conn = JDBCutil.getConnection();) {
            StringBuilder sb = new StringBuilder();
            sb.append(" delete from emp where empno = ? ");

            PreparedStatement preparedStatement = conn.prepareStatement(sb.toString());

            int index = 0;
            preparedStatement.setInt(++index, empno);

            //执行sql语句
            int rs = preparedStatement.executeUpdate();
            System.out.println("删除了" + rs + "条记录");

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


    }

    public void updateEmp(EmpPojo input) {


        {
            try (Connection conn = JDBCutil.getConnection();) {
                conn.setAutoCommit(false);
                String sql = "update emp set ename = ?, deptno = (select deptno from dept where dname = ? )\n" +
                        " where empno = ? ";

                PreparedStatement preparedStatement = conn.prepareStatement(sql);

                int index = 0;
                preparedStatement.setString(++index, input.getEname());
                preparedStatement.setString(++index, input.getDname());
                preparedStatement.setInt(++index, input.getEmpno());

                //执行sql语句
                int rs = preparedStatement.executeUpdate();
                if (rs != 1) {
                    conn.rollback();
                    throw new RuntimeException("有问题联系管理员");
                }
                System.out.println("更新了" + rs + "条记录");

            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public void insertEmp(EmpPojo input) {


        try (Connection conn = JDBCutil.getConnection();) {
            conn.setAutoCommit(false);
            String sql = "insert into emp(empno, ename, deptno) values (?, ?,  (select deptno from dept where dname = ?))";

            PreparedStatement preparedStatement = conn.prepareStatement(sql);

            int index = 0;
            preparedStatement.setInt(++index, input.getEmpno());
            preparedStatement.setString(++index, input.getEname());
            preparedStatement.setString(++index, input.getDname());


            //执行sql语句
            int rs = preparedStatement.executeUpdate();
            if (rs != 1) {
                conn.rollback();
                throw new RuntimeException("有问题联系管理员");
            }
            System.out.println("更新了" + rs + "条记录");

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
}
